﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.OpenXml4Net.OPC;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace DBD.Common.NPOI
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 读取IWorkbook
        /// </summary>
        /// <param name="fullPath"></param>
        /// <returns></returns>
        public static IWorkbook ReadWorkbook(string fullPath)
        {
            string extension = Path.GetExtension(fullPath);   //.txt
            using (FileStream readfile = new FileStream(fullPath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                if (extension == ".xlsx") // 2007版本
                    workbook = new XSSFWorkbook(readfile);
                else if (extension == ".xls") // 2003版本
                    workbook = new HSSFWorkbook(readfile);
                else
                    throw new Exception("文件扩展名不匹配");

                readfile.Close();
                return workbook;
            }
        }


        /// <summary>
        /// 读取第一张工作表
        /// </summary>
        /// <param name="fullPath">完整路径</param>
        /// <param name="isColumnName">起始行是否是DataTable的列名</param>
        /// <param name="rowIndex">起始行索引</param>
        /// <returns></returns>
        public static DataTable ReadSheet(string fullPath, bool isColumnName, int rowIndex = 0)
        {
            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            if (sheet == null)
                throw new Exception("未找到工作表");

            //3、创建DataTable 的列
            DataTable table = new DataTable();
            IRow firstRow = sheet.GetRow(rowIndex);

            if (isColumnName)
            {
                CreateDataColumn(table, firstRow);
                rowIndex = rowIndex + 1;
            }
            else
            {
                CreateDataColumn(table, firstRow.LastCellNum);
            }

            //4、灌入数据
            CreateDataRow(table, sheet, rowIndex);
            return table;
        }

        /// <summary>
        /// 读取第一张工作表
        /// 起始行必须是列头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fullPath">完整路径</param>
        /// <param name="refreshColumnNameParam">刷新列名(Key为模型属性，Value为Excel字段)</param>
        /// <param name="rowIndex">起始行索引</param>
        /// <returns></returns>
        public static IEnumerable<T> ReadSheet<T>(string fullPath, Dictionary<string, string> refreshColumnNameParam, int rowIndex = 0)
        {
            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            if (sheet == null)
                throw new Exception("未找到工作表");

            //3、创建重命名字典
            IRow firstRow = sheet.GetRow(rowIndex);
            Dictionary<string, string> dicParam = refreshColumnNameParam.ToDictionary(x => x.Value, x => x.Key);
            Dictionary<string, int> dic = new Dictionary<string, int>();
            for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
            {
                ICell cell = firstRow.GetCell(i);
                string cellValue = cell.StringCellValue;

                if (dicParam.ContainsKey(cellValue))
                {
                    dic.Add(dicParam[cellValue], i);
                }
            }
            rowIndex = rowIndex + 1;

            //4、灌入数据

            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            List<T> list = new List<T>();

            for (int i = rowIndex; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i);

                if (row == null)
                    continue; //没有数据的行默认是null

                T instance = Activator.CreateInstance<T>();
                foreach (PropertyInfo property in properties)
                {
                    if (dic.ContainsKey(property.Name))
                    {
                        property.SetValue(instance, ToPropertyInfoValue(row.GetCell(dic[property.Name]), property), null);
                    }

                }
                list.Add(instance);
            }
            return list;
        }

        /// <summary>
        /// 读取第一张工作表
        /// 起始行是数据，不是列头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fullPath">完整路径</param>
        /// <param name="refreshColumnNameParam">刷新列名(Key为模型属性，Value为Excel字段索引)</param>
        /// <param name="rowIndex">起始行索引</param>
        /// <returns></returns>
        public static IEnumerable<T> ReadSheet<T>(string fullPath, Dictionary<string, int> refreshColumnNameParam, int rowIndex = 0)
        {
            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            if (sheet == null)
                throw new Exception("未找到工作表");

            //3、灌入数据
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            List<T> list = new List<T>();

            for (int i = rowIndex; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i);

                if (row == null)
                    continue; //没有数据的行默认是null

                T instance = Activator.CreateInstance<T>();
                foreach (PropertyInfo property in properties)
                {
                    if (refreshColumnNameParam.ContainsKey(property.Name))
                    {
                        property.SetValue(instance, ToPropertyInfoValue(row.GetCell(refreshColumnNameParam[property.Name]), property), null);
                    }

                }
                list.Add(instance);
            }
            return list;

        }


        /// <summary>
        /// 导出第一张工作表
        /// 起始行必须是列头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fullPath">模板路径</param>
        /// <param name="outFullPath">导出路径</param>
        /// <param name="datas">数据集</param>
        /// <param name="refreshColumnNameParam">刷新列名(Key为模型属性，Value为Excel字段)</param>
        public static void WriteSheet<T>(string fullPath, string outFullPath, IEnumerable<T> datas,
            Dictionary<string, string> refreshColumnNameParam)
        {
            if (Path.GetExtension(fullPath) != Path.GetExtension(outFullPath))
                throw new Exception("模板文件类型与传出文件类型不匹配");

            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            //3、创建重命名字典
            IRow firstRow = sheet.GetRow(0);
            Dictionary<string, string> dicParam = refreshColumnNameParam.ToDictionary(x => x.Value, x => x.Key);
            Dictionary<int, string> dic = new Dictionary<int, string>();
            for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
            {
                ICell cell = firstRow.GetCell(i);
                string cellValue = cell.StringCellValue;

                if (dicParam.ContainsKey(cellValue))
                {
                    dic.Add(i, dicParam[cellValue]);
                }
            }

            //4、灌入数据
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            foreach (T data in datas)
            {
                sheet.CreateRow(sheet.LastRowNum + 1);
                IRow row = sheet.GetRow(sheet.LastRowNum);

                for (int i = 0; i < firstRow.LastCellNum; i++)
                {
                    PropertyInfo property = properties.First(x => x.Name == dic[i]);
                    ToCellValue(property, property.GetValue(data, null), row, i);
                }
            }

            FileStream writefile = new FileStream(outFullPath, FileMode.Create, FileAccess.Write);
            workbook.Write(writefile);
            writefile.Close();
        }

        /// <summary>
        /// 导出第一张工作表
        /// 起始行是数据，不是列头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fullPath">模板路径</param>
        /// <param name="outFullPath">导出路径</param>
        /// <param name="datas">数据集</param>
        /// <param name="refreshColumnNameParam">刷新列名(Key为模型属性，Value为Excel字段索引)</param>
        public static void WriteSheet<T>(string fullPath, string outFullPath, IEnumerable<T> datas,
            Dictionary<string, int> refreshColumnNameParam)
        {
            if (Path.GetExtension(fullPath) != Path.GetExtension(outFullPath))
                throw new Exception("模板文件类型与传出文件类型不匹配");

            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            //3、创建重命名字典
            IRow firstRow = sheet.GetRow(0);
            Dictionary<int, string> dic = refreshColumnNameParam.ToDictionary(x => x.Value, x => x.Key);


            //4、灌入数据
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            foreach (T data in datas)
            {
                sheet.CreateRow(sheet.LastRowNum + 1);
                IRow row = sheet.GetRow(sheet.LastRowNum);

                for (int i = 0; i < firstRow.LastCellNum; i++)
                {
                    PropertyInfo property = properties.First(x => x.Name == dic[i]);
                    ToCellValue(property, property.GetValue(data, null), row, i);
                }
            }

            FileStream writefile = new FileStream(outFullPath, FileMode.Create, FileAccess.Write);
            workbook.Write(writefile);
            writefile.Close();
        }

        /// <summary>
        /// 导出第一张工作表
        /// 起始行必须是列头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fullPath">模板路径</param>
        /// <param name="datas">数据集</param>
        /// <param name="refreshColumnNameParam">刷新列名(Key为模型属性，Value为Excel字段)</param>
        public static MemoryStream WriteSheet<T>(string fullPath, IEnumerable<T> datas,
            Dictionary<string, string> refreshColumnNameParam)
        {
            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            //3、创建重命名字典
            IRow firstRow = sheet.GetRow(0);
            Dictionary<string, string> dicParam = refreshColumnNameParam.ToDictionary(x => x.Value, x => x.Key);
            Dictionary<int, string> dic = new Dictionary<int, string>();
            for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
            {
                ICell cell = firstRow.GetCell(i);
                string cellValue = cell.StringCellValue;

                if (dicParam.ContainsKey(cellValue))
                {
                    dic.Add(i, dicParam[cellValue]);
                }
            }

            //4、灌入数据
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            foreach (T data in datas)
            {
                sheet.CreateRow(sheet.LastRowNum + 1);
                IRow row = sheet.GetRow(sheet.LastRowNum);

                for (int i = 0; i < firstRow.LastCellNum; i++)
                {
                    PropertyInfo property = properties.First(x => x.Name == dic[i]);
                    ToCellValue(property, property.GetValue(data, null), row, i);
                }
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream;
        }

        /// <summary>
        /// 导出第一张工作表
        /// 起始行是数据，不是列头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fullPath">模板路径</param>
        /// <param name="datas">数据集</param>
        /// <param name="refreshColumnNameParam">刷新列名(Key为模型属性，Value为Excel字段索引)</param>
        public static MemoryStream WriteSheet<T>(string fullPath, IEnumerable<T> datas,
            Dictionary<string, int> refreshColumnNameParam)
        {
            IWorkbook workbook = ReadWorkbook(fullPath);

            //2、获取第一张工作表
            ISheet sheet = workbook.GetSheetAt(0);

            //3、创建重命名字典
            IRow firstRow = sheet.GetRow(0);
            Dictionary<int, string> dic = refreshColumnNameParam.ToDictionary(x => x.Value, x => x.Key);


            //4、灌入数据
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            foreach (T data in datas)
            {
                sheet.CreateRow(sheet.LastRowNum + 1);
                IRow row = sheet.GetRow(sheet.LastRowNum);

                for (int i = 0; i < firstRow.LastCellNum; i++)
                {
                    PropertyInfo property = properties.First(x => x.Name == dic[i]);
                    ToCellValue(property, property.GetValue(data, null), row, i);
                }
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream;
        }




        //private

        /// <summary>
        /// 创建DataTable的列
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="columnNum">列数</param>
        private static void CreateDataColumn(DataTable table, int columnNum)
        {
            for (int i = 0; i < columnNum; i++)
            {
                DataColumn column = new DataColumn("Column" + i);
                table.Columns.Add(column);
            }
        }

        /// <summary>
        /// 创建DataTable的列
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="firstRow">Excel的行头</param>
        private static void CreateDataColumn(DataTable table, IRow firstRow)
        {
            for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
            {
                ICell cell = firstRow.GetCell(i);
                if (cell == null)
                    continue;
                string cellValue = cell.StringCellValue;
                if (cellValue == null)
                    continue;
                DataColumn column = new DataColumn(cellValue);
                table.Columns.Add(column);
            }
        }

        /// <summary>
        /// 创建DataTable的行（灌入数据）
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="sheet">工作表</param>
        /// <param name="rowIndex">起始行索引</param>
        private static void CreateDataRow(DataTable table, ISheet sheet, int rowIndex)
        {
            int rowCount = sheet.LastRowNum; //总行数

            for (int i = rowIndex; i <= rowCount; ++i)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    continue; //没有数据的行默认是null

                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
                {
                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                        dataRow[j] = row.GetCell(j).ToString();
                }
                table.Rows.Add(dataRow);
            }
        }

        /// <summary>
        /// 数据类型转换
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="property"></param>
        /// <returns></returns>
        private static object ToPropertyInfoValue(ICell obj, PropertyInfo property)
        {
            if (property.PropertyType == obj.CellType.GetType())
                return obj;
            if (property.PropertyType == typeof(decimal))
            {
                return decimal.Parse(obj.ToString());
            }
            if (property.PropertyType == typeof(int))
            {
                return int.Parse(obj.ToString());
            }
            if (property.PropertyType == typeof(float))
            {
                return float.Parse(obj.ToString());
            }
            if (property.PropertyType == typeof(double))
            {
                return double.Parse(obj.ToString());
            }
            if (property.PropertyType == typeof(string))
            {
                if (obj.CellType == CellType.NUMERIC && obj.DateCellValue != DateTime.MinValue)
                {
                    return obj.DateCellValue.ToString();
                }
            }
            if (property.PropertyType == typeof(DateTime))
            {
                if (obj.CellType == CellType.NUMERIC && obj.DateCellValue != DateTime.MinValue)
                {
                    return obj.DateCellValue;
                }
                return DateTime.Parse(obj.ToString());
            }

            return obj.ToString();
        }

        /// <summary>
        /// 写入数据到Excel表格
        /// </summary>
        /// <param name="property"></param>
        /// <param name="obj"></param>
        /// <param name="row"></param>
        /// <param name="cellIndex"></param>
        private static void ToCellValue(PropertyInfo property, object obj, IRow row, int cellIndex)
        {
            if (property.PropertyType == typeof(int) || property.PropertyType == typeof(double) ||
                property.PropertyType == typeof(float) || property.PropertyType == typeof(decimal))
            {
                row.CreateCell(cellIndex, CellType.NUMERIC);
                row.GetCell(cellIndex).SetCellValue(double.Parse(obj.ToString()));
            }
            if (property.PropertyType == typeof(DateTime))
            {
                row.CreateCell(cellIndex, CellType.NUMERIC);
                row.GetCell(cellIndex).SetCellValue(DateTime.Parse(obj.ToString()));
            }
            row.CreateCell(cellIndex, CellType.STRING);
            row.GetCell(cellIndex).SetCellValue(obj.ToString());
        }

    }


}